﻿using CommonUtils.CodeFactory;
using System;
using System.Linq;
using System.Reflection;
using System.Text;

namespace CommonUtils
{
    /// <summary>
    /// 泛型操作（属性名与字段名一致） T必须包含无参数构造函数
    /// </summary>
    public abstract partial class DbBase
    {
        public T[] SelectEntities<T>(string sql, StringObjectMap args = null)
        {
            var type = typeof(T);
            var table = GetDataTable(sql, args);
            return table.ToArray<T>();
        }

        public T[] GetEntities<T>(string sql, StringObjectMap args = null)
        => SelectEntities<T>(sql, args);

        public T SelectEntity<T>(string sql, StringObjectMap args = null)
        => SelectEntities<T>(sql, args).FirstOrDefault();

        public T GetEntity<T>(string sql, StringObjectMap args = null)
        => SelectEntity<T>(sql, args);

        public T[] SelectAllEntities<T>()
        {
            var type = typeof(T);
            var table = GetEntireDataTable(type.Name);
            return table.ToArray<T>();
        }

        public T[] GetAllEntities<T>()
        => SelectAllEntities<T>();

        /// <summary>
        /// (...) values (...)
        /// </summary>
        private string GetValuesSql<T>(T value, out StringObjectMap args, string keyName = null, params string[] updateIgnoreKeyNames)
        {
            var type = typeof(T);
            var columns = new StringBuilder();
            var values = new StringBuilder();
            args = new StringObjectMap();
            foreach (var member in type.GetMembers())
            {
                if (updateIgnoreKeyNames.Contains(member.Name))
                    continue;

                //属性
                if (member.MemberType == MemberTypes.Property)
                {
                    var property = (PropertyInfo)member;

                    //忽略只读属性
                    if (!property.CanWrite)
                        continue;

                    args.Add(ParameterIdentifier + member.Name, property.GetValue(value));
                }
                //字段
                else if (member.MemberType == MemberTypes.Field)
                    args.Add(ParameterIdentifier + member.Name, ((FieldInfo)member).GetValue(value));
                //其他
                else
                    continue;

                //keyName为更新时的主键，只进参数，不进valuesSql
                if (!string.IsNullOrEmpty(keyName) && member.Name == keyName)
                    continue;

                if (columns.Length > 0)
                    columns.Append(',');

                if (values.Length > 0)
                    values.Append(',');

                columns.Append(FieldStartIdentifier);
                columns.Append(member.Name);
                columns.Append(FieldEndIdentifier);

                values.Append(ParameterIdentifier);
                values.Append(member.Name);
            }
            return string.Format("({0}) values ({1})", columns, values);
        }

        /// <summary>
        /// a1=@ai,a2=@a2...
        /// </summary>
        private string GetSetSql<T>(T value, out StringObjectMap args, string keyName = null, params string[] updateIgnoreKeyNames)
        {
            var type = typeof(T);
            var valuesSql = new StringBuilder();
            args = new StringObjectMap();
            foreach (var member in type.GetMembers())
            {
                if (updateIgnoreKeyNames.Contains(member.Name))
                    continue;

                if (member.MemberType == MemberTypes.Property)
                    args.Add(ParameterIdentifier + member.Name, ((PropertyInfo)member).GetValue(value));
                else if (member.MemberType == MemberTypes.Field)
                    args.Add(ParameterIdentifier + member.Name, ((FieldInfo)member).GetValue(value));
                else
                    continue;

                //keyName为更新时的主键，只进参数，不进valuesSql
                if (!string.IsNullOrEmpty(keyName) && member.Name == keyName)
                    continue;

                if (valuesSql.Length > 0)
                    valuesSql.Append(',');

                valuesSql.Append(FieldStartIdentifier);
                valuesSql.Append(member.Name);
                valuesSql.Append(FieldEndIdentifier);
                valuesSql.Append('=');
                valuesSql.Append(ParameterIdentifier);
                valuesSql.Append(member.Name);
            }
            return valuesSql.ToString();
        }

        //insert into ?? (,,) values (,,);
        public bool Insert<T>(T value, string tableName)
        {
            var valuesSql = GetValuesSql(value, out StringObjectMap args);
            var insertSql = string.Format("insert into {0}{2}{1} {3};", FieldStartIdentifier, FieldEndIdentifier, tableName, valuesSql);
            return GetExecuteResult(insertSql, args);
        }

        public bool Insert<T>(T value)
        => Insert(value, typeof(T).Name);

        public bool Update<T>(T value, string tableName, string keyName, params string[] updateIgnoreKeyNames)
        {
            var valuesSql = GetSetSql(value, out StringObjectMap args, keyName, updateIgnoreKeyNames);
            var updateSql = string.Format("update {0}{3}{1} set {4} where {0}{5}{1}={2}{5};", FieldStartIdentifier, FieldEndIdentifier, ParameterIdentifier, tableName, valuesSql, keyName);
            return GetExecuteResult(updateSql, args);
        }

        public bool Delete<T>(string name, string value)
        {
            var deleteSql = string.Format("delete from {0}{3}{1} where {0}{4}{1}={2}{4};", FieldStartIdentifier, FieldEndIdentifier, ParameterIdentifier, typeof(T).Name, name);
            return GetExecuteNumber(deleteSql, new StringObjectMap(name, value)) > 0;
        }

        public bool Exist<T>(T value, string tableName, string keyName)
        {
            var type = typeof(T);
            var selectSql = string.Format("select count(*) from {0}{3}{1} where {0}{4}{1}={2}{4};", FieldStartIdentifier, FieldEndIdentifier, ParameterIdentifier, tableName, keyName);
            return SelectIntger(selectSql, new StringObjectMap(keyName, type.GetProperty(keyName).GetValue(value))) > 0;
        }

        public bool UpdateOrInsert<T>(T value, string tableName, string keyName, params string[] updateIgnoreKeyNames)
        {
            if (Exist(value, tableName, keyName))
                return Update(value, tableName, keyName, updateIgnoreKeyNames);
            return Insert(value, tableName);
        }

        public bool IgnoreOrInsert<T>(T value, string tableName, string keyName)
        {
            if (Exist(value, tableName, keyName))
                return false;
            return Insert(value, tableName);
        }

        public bool IsColumnLost(Type type, out MemberInfo lostMember)
        {
            var fieldNames = GetColumnNames(type.Name).Select(m => m.ToUpper()).ToArray();
            foreach (var member in type.GetMembers())
            {
                if (member.MemberType == MemberTypes.Property || member.MemberType == MemberTypes.Field)
                {
                    if (!fieldNames.Contains(member.Name.ToUpper()))
                    {
                        lostMember = member;
                        return true;
                    }
                }
            }
            lostMember = null;
            return false;
        }

        public void VerifyTable<T>()
        {
            var type = typeof(T);
            if (TableExists(type.Name))
            {
                while (IsColumnLost(type, out MemberInfo member))
                {
                    var alterSql = "";

                    switch (member.MemberType)
                    {
                        case MemberTypes.Field:
                            alterSql = string.Format("ALTER TABLE {0}{2}{1} ADD COLUMN {0}{3}{1} {4};", FieldStartIdentifier, FieldEndIdentifier, type.Name, member.Name, ((FieldInfo)member).FieldType.GetDbIdentifier());
                            break;

                        case MemberTypes.Property:
                            alterSql = string.Format("ALTER TABLE {0}{2}{1} ADD COLUMN {0}{3}{1} {4};", FieldStartIdentifier, FieldEndIdentifier, type.Name, member.Name, ((PropertyInfo)member).PropertyType.GetDbIdentifier());
                            break;
                    }

                    Execute(alterSql);
                }
            }
            else
            {
                var createSql = new StringBuilder();
                createSql.AppendLine("CREATE TABLE {0}{2}{1} (", FieldStartIdentifier, FieldEndIdentifier, type.Name);

                var first = true;
                var members = type.GetMembers();
                for (int index = 0; index < members.Length; index++)
                {
                    var member = members[index];
                    switch (member.MemberType)
                    {
                        case MemberTypes.Field:
                            if (!first)
                                createSql.AppendLine(",");
                            else
                                first = false;
                            var field = (FieldInfo)member;
                            createSql.AppendFormat(" {0}{2}{1} {3}", FieldStartIdentifier, FieldEndIdentifier, member.Name, field.FieldType.GetDbIdentifier());
                            break;

                        case MemberTypes.Property:
                            if (!first)
                                createSql.AppendLine(",");
                            else
                                first = false;
                            var property = (PropertyInfo)member;
                            createSql.AppendFormat(" {0}{2}{1} {3}", FieldStartIdentifier, FieldEndIdentifier, member.Name, property.PropertyType.GetDbIdentifier());
                            break;
                    }
                }

                createSql.AppendLine();
                createSql.AppendLine(");");
                Execute(createSql.ToString());
            }
        }
    }
}
